package org.expressme.employee.mgmt.web;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

/**
 * Init memory-based database.
 * 
 * @author Michael Liao (askxuefeng@gmail.com)
 */
public class InitMemdbListener implements ServletContextListener {

    public void contextInitialized(ServletContextEvent sce) {
        try {
            Class.forName("org.hsqldb.jdbcDriver");
            Connection c = getDirectConnection();
            Statement stmt = c.createStatement();
            stmt.execute("create table employee (id varchar(32) primary key, name varchar(50) not null, title varchar(50) not null, gender bit not null, birth date not null)");
            stmt.execute("insert into employee (id, name, title, gender, birth) values ('7105629a4b9a4f079ba129a1e56bdc43', 'Michael', 'CEO', true, '1982-1-1')");
            stmt.execute("insert into employee (id, name, title, gender, birth) values ('3d9698f2efc648f692671af0f51c3a8c', 'Tracy', 'Project Manager', false, '1982-1-1')");
            stmt.execute("insert into employee (id, name, title, gender, birth) values ('5a65f968598f4746a2a45177e3550793', 'Bob', 'Architect', true, '1980-1-1')");
            stmt.execute("insert into employee (id, name, title, gender, birth) values ('64bfc4d599c7485195911cf878452647', 'Joshua', 'Engineer', true, '1985-5-5')");
            stmt.execute("insert into employee (id, name, title, gender, birth) values ('89d12caa264c45079f47094452a0e514', 'Eva', 'Designer', false, '1986-6-6')");
            stmt.execute("insert into employee (id, name, title, gender, birth) values ('89d12caa264c45079f47094452a0e515', 'beck', 'Designer', false, '1986-6-6')");
            stmt.close();
            c.close();
        }
        catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    Connection getDirectConnection() {
        try {
            return DriverManager.getConnection("jdbc:hsqldb:mem:employee", "sa", "");
        }
        catch(SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void contextDestroyed(ServletContextEvent sce) {
    }

}
